﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for clsDONGPHI
/// </summary>
public class clsDONGPHI
{
	public clsDONGPHI()
	{
		//
		// TODO: Add constructor logic here
		//
    }

    #region Get By Id
    public static DataTable GetById(Guid F_STT)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@F_STT", F_STT);
        try {
            dt = connect.GetSelectCommand("DONGPHI_GetById", para);
        }
        catch { }
        return dt;
    }
    #endregion

    public static DataTable BaoCaoThuPhiTheoLop_GetByF_MADT(string F_MADT)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@F_MADT", F_MADT);
        try
        {
            dt = connect.GetSelectCommand("DONGPHI_BaoCaoThuPhiTheoLop", para);
        }
        catch { }
        return dt;
    }
    public static DataTable SumBaoCaoThuPhiTheoLop_GetByF_MADT(string F_MADT)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@F_MADT", F_MADT);
        try
        {
            dt = connect.GetSelectCommand("DONGPHI_SumBaoCaoThuPhiTheoLop", para);
        }
        catch { }
        return dt;
    }
    public static DataTable BaoCaoThuPhiTheoLop_GetByF_MALOP_F_TUNGAY_F_DENNGAY(string F_MALOP, DateTime F_TUNGAY, DateTime F_DENNGAY)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        SqlParameter[] para = new SqlParameter[3];
        para[0] = new SqlParameter("@F_MALOP", F_MALOP);
        para[1] = new SqlParameter("@F_TUNGAY", F_TUNGAY);
        para[2] = new SqlParameter("@F_DENNGAY", F_DENNGAY);
        try
        {
            dt = connect.GetSelectCommand("DONGPHI_BaoCaoThuPhiTheoLop_GetByF_MALOP_F_TUNGAY_F_DENNGAY", para);
        }
        catch { }
        return dt;
    }

    #region Get By Ma Hoc Vien
    public static DataTable GetByStudentCode(string F_MAHV)
    { 
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@F_MAHV", F_MAHV);

        try
        {
            dt = connect.GetSelectCommand("DONGPHI_GetByMaHV", para);
        }
        catch 
        { }
        return dt;
    }
    #endregion

    #region Insert
    public static string Insert(Guid F_STT, string F_MAHV, int F_MANV, int F_STTCAPLOP, DateTime F_NGAYDONGPHI, string F_MADT, int F_LANTHU, float F_SOTHANG, decimal F_SOTIENQD, decimal F_SOTIENPHAT, decimal F_SOTIENHB, decimal F_TIENTHUCTHU, string F_GHICHU, string F_TIENBANGCHU, string F_NOIDUNGTHU)
    {
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = {F_STT, F_MAHV, F_MANV, F_STTCAPLOP, F_NGAYDONGPHI, F_MADT, F_LANTHU, F_SOTHANG, F_SOTIENQD, F_SOTIENPHAT, F_SOTIENHB, F_TIENTHUCTHU, F_GHICHU, F_TIENBANGCHU, F_NOIDUNGTHU };
        string[] parameters = { "@F_STT", "@F_MAHV", "@F_MANV", "@F_STTCAPLOP", "@F_NGAYDONGPHI", "F_MADT", "@F_LANTHU", "@F_SOTHANG", "@F_SOTIENQD", "@F_SOTIENPHAT", "@F_SOTIENHB", "@F_TIENTHUCTHU", "@F_GHICHU", "@F_TIENBANGCHU", "@F_NOIDUNGTHU" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }
        try
        {
            connect.ExecuteQuery("DONGPHI_Insert", para);
            return "OK";
        }
        catch
        {
            return "ERR";
        }
    }
    #endregion

    #region Insert Phí khác
    public static string Insert_PhiKhac(Guid F_STT, string F_MAHV, int F_MANV, int F_STTCAPLOP, DateTime F_NGAYDONGPHI, decimal F_TIENTHUCTHU, string F_GHICHU, string F_TIENBANGCHU, string F_MALOP, string F_NOIDUNGTHU)
    {
        DatabaseConnect connect = new DatabaseConnect();
        // Tham số
        object[] values = { F_STT, F_MAHV, F_MANV, F_STTCAPLOP, F_NGAYDONGPHI, F_TIENTHUCTHU, F_GHICHU, F_TIENBANGCHU, F_MALOP, F_NOIDUNGTHU };
        string[] parameters = { "@F_STT", "@F_MAHV", "@F_MANV", "@F_STTCAPLOP", "@F_NGAYDONGPHI", "@F_TIENTHUCTHU", "@F_GHICHU", "@F_TIENBANGCHU", "@F_MALOP", "@F_NOIDUNGTHU" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            connect.ExecuteQuery("DONGPHI_Insert_PhiKhac", para);
            return "OK";
        }   
        catch
        {
            return "ERR";
        }
    }
    #endregion

    #region
    public static string RutPhi(Guid F_STT, string F_MAHV, int F_MANV, int F_STTCAPLOP, DateTime F_NGAYDONGPHI, decimal F_TIENTHUCTHU, string F_GHICHU, string F_TIENBANGCHU, string F_MALOP, string F_NOIDUNGTHU, DateTime F_NGAYRUTPHI)
    {
        DatabaseConnect connect = new DatabaseConnect();
        // Tham số
        object[] values = { F_STT, F_MAHV, F_MANV, F_STTCAPLOP, F_NGAYDONGPHI, F_TIENTHUCTHU, F_GHICHU, F_TIENBANGCHU, F_MALOP, F_NOIDUNGTHU, F_NGAYRUTPHI };
        string[] parameters = { "@F_STT", "@F_MAHV", "@F_MANV", "@F_STTCAPLOP", "@F_NGAYDONGPHI", "@F_TIENTHUCTHU", "@F_GHICHU", "@F_TIENBANGCHU", "@F_MALOP", "@F_NOIDUNGTHU", "@F_NGAYRUTPHI" };
        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            connect.ExecuteQuery("DONGPHI_RutPhi", para);
            return "OK";
        }
        catch
        {
            return "ERR";
        }
    }
    #endregion

    #region Update
    public static string Update(Guid F_STT, int F_STTCAPLOP, DateTime F_NGAYDONGPHI, int F_LANTHU, float F_SOTHANG, decimal F_SOTIENQD, decimal F_SOTIENPHAT, decimal F_SOTIENHB, decimal F_TIENTHUCTHU, string F_GHICHU, string F_TIENBANGCHU)
    {
        DatabaseConnect connect = new DatabaseConnect();
        
        object[] values = { F_STT, F_STTCAPLOP, F_NGAYDONGPHI, F_LANTHU, F_SOTHANG, F_SOTIENQD, F_SOTIENPHAT, F_SOTIENHB, F_TIENTHUCTHU, F_GHICHU, F_TIENBANGCHU };
        string[] parameters = { "@F_STT", "@F_STTCAPLOP", "@F_NGAYDONGPHI", "@F_LANTHU", "@F_SOTHANG", "@F_SOTIENQD", "@F_SOTIENPHAT", "@F_SOTIENHB", "@F_TIENTHUCTHU", "@F_GHICHU", "@F_TIENBANGCHU" };
        // Tham so
        SqlParameter[] para = new SqlParameter[parameters.Length];
        // Gan gia tri tham so
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
            
        }
        try
        {
            connect.ExecuteQuery("DONGPHI_Update", para);
            return "OK";
        }
        catch
        {
            return "ERR";
        }        
    }
    #endregion

    #region Delete
    public static string Delete(Guid F_STT)
    {
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@F_STT", F_STT);
        try
        {
            connect.ExecuteQuery("DONGPHI_Delete", para);
            return "OK";
        }
        catch(SqlException ex) {
            return ex.Message;
        }
    }
    #endregion

    #region Get By Hoc Vien va Dot Thu
    public static DataTable GetByHocVienDotThu(string F_MAHV, string F_MADT)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        SqlParameter[] para = new SqlParameter[2];
        para[0] = new SqlParameter("@F_MAHV", F_MAHV);
        para[1] = new SqlParameter("@F_MADT", F_MADT);
        try {
            dt = connect.GetSelectCommand("DONGPHI_GetBy_HocVien_DotThu", para);
        }
        catch { }
        return dt;
    }
    #endregion

    #region Tăng số lần đóng phí
    public static int IncreaseTurn(string F_MAHV, string F_MADT)
    {
        int F_LANTHU = 0;
        DataTable dt = GetByHocVienDotThu(F_MAHV, F_MADT);
        if (dt.Rows.Count > 0)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                int temp = 0;
                try {
                    temp = int.Parse(dt.Rows[i]["F_LANTHU"].ToString());
                }
                catch { }
                if(temp > F_LANTHU)
                {
                    F_LANTHU = temp;
                }
            }
            F_LANTHU++;
        }
        else
        {
            F_LANTHU++;
        }
        
        return F_LANTHU;
    }
    #endregion

    #region SumFee
    public static DataTable SumFeeStudent(string F_MAHV, string F_MALOP)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        SqlParameter[] para = new SqlParameter[2];
        para[0] = new SqlParameter("@F_MAHV", F_MAHV);
        para[1] = new SqlParameter("@F_MALOP", F_MALOP);
        try
        {
            dt = connect.GetSelectCommand("DONGPHI_SumFeeStudent", para);
        }
        catch
        { }
        return dt;
    }
    #endregion

    #region SumFeeReturn
    public static DataTable SumFeeReturnStudent(string F_MAHV, string F_MALOP)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        SqlParameter[] para = new SqlParameter[2];
        para[0] = new SqlParameter("@F_MAHV", F_MAHV);
        para[1] = new SqlParameter("@F_MALOP", F_MALOP);
        try
        {
            dt = connect.GetSelectCommand("DONGPHI_SumFeeReturn", para);
        }
        catch
        { }
        return dt;
    }
    #endregion

    #region SumFeeStudent
    public static decimal GetSumFeeStudent(string F_MAHV, string F_MALOP)
    {
        decimal SumFee = -1;
        decimal SumFeeReturn = 0;
        DataTable dt = SumFeeStudent(F_MAHV, F_MALOP);
        if (dt.Rows[0]["SUMFEE"] != DBNull.Value)
        {
            SumFee = decimal.Parse(dt.Rows[0]["SUMFEE"].ToString());
        }

        DataTable dt1 = SumFeeReturnStudent(F_MAHV, F_MALOP);
        if (dt1.Rows[0]["SUMFEE"] != DBNull.Value)
        {
            SumFeeReturn = decimal.Parse(dt1.Rows[0]["SUMFEE"].ToString());
        }
        SumFee = SumFee - SumFeeReturn;
        return SumFee;
    }
    #endregion
}